﻿CREATE VIEW [dbo].[StandardSailsView]
AS
SELECT     dbo.OEMSailList_Tbl.OEMSailListID, dbo.OEMSailList_Tbl.BoatGenID, dbo.OEMSailList_Tbl.OEMIdentifier, dbo.OEMSailList_Tbl.CustomerIdentifier, 
                      dbo.OEMSailList_Tbl.CustomerID, dbo.OEMSailList_Tbl.MarketID, dbo.OEMSailList_Tbl.QuoteSailTypeID, dbo.OEMSailList_Tbl.Area, 
                      dbo.OEMSailList_Tbl.GenericYarnID, dbo.OEMSailList_Tbl.FilmTypeID, dbo.OEMSailList_Tbl.DPI, dbo.OEMSailList_Tbl.CurrencyID, 
                      dbo.OEMSailList_Tbl.TierID, dbo.OEMSailList_Tbl.UsageID, dbo.OEMSailList_Tbl.Createdate, dbo.OEMSailList_Tbl.ConstructionID, 
                      dbo.OEMSailList_Tbl.MarketRetailPrice, dbo.OEMSailList_Tbl.ListRetailPrice, ROUND(dbo.OEMSailList_Tbl.OEMRetailPrice, 0) AS OEMRetailPrice, 
                      dbo.OEMSailList_Tbl.Cut, dbo.OEMSailList_Tbl.Weight, dbo.OEMSailList_Tbl.AccessoryID, dbo.OEMSailList_Tbl.SailNumberColourID, 
                      dbo.OEMSailList_Tbl.AccessoryColourID, dbo.OEMSailList_Tbl.NylonColourDetails, dbo.OEMSailList_Tbl.FabricID, dbo.OEMSailList_Tbl.NotionalArea, 
                      dbo.OEMSailList_Tbl.TopSurfaceID, dbo.OEMSailList_Tbl.BotSurfaceID, dbo.OEMSailList_Tbl.GenSailTypeID, dbo.OEMSailList_Tbl.Notes, 
                      dbo.OEMSailList_Tbl.OptionID, dbo.OEMSailList_Tbl.DesignFile, dbo.OEMSailList_Tbl.Standard, dbo.OEMSailList_Tbl.QuoteID, 
                      dbo.OEMSailList_Tbl.LineItem, dbo.OEMSailList_Tbl.QuoteLineItemID, CASE WHEN dbo.OEMSailList_Tbl.AxaptaCode IS NOT NULL 
                      THEN dbo.OEMSailList_Tbl.AxaptaCode ELSE '0' END AS AxaptaCode,
                          (SELECT     SailType
                            FROM          dbo.QuoteSailType_Tbl
                            WHERE      (QuoteSailTypeID = dbo.OEMSailList_Tbl.QuoteSailTypeID)) AS SailType,
                          (SELECT     YarnType
                            FROM          dbo.GenericYarn_Tbl
                            WHERE      (GenericYarnID = dbo.OEMSailList_Tbl.GenericYarnID)) AS Yarntype,
                          (SELECT     FilmType
                            FROM          dbo.FilmType_Tbl
                            WHERE      (FilmTypeID = dbo.OEMSailList_Tbl.FilmTypeID)) AS FilmType,
                          (SELECT     Tier
                            FROM          dbo.Tier_Tbl
                            WHERE      (TierID = dbo.OEMSailList_Tbl.TierID)) AS Tier,
                          (SELECT     TierPriceIndicator
                            FROM          dbo.Tier_Tbl AS Tier_Tbl_1
                            WHERE      (TierID = dbo.OEMSailList_Tbl.TierID)) AS TierPriceIndicator,
                          (SELECT     UsageType
                            FROM          dbo.Usage_Tbl
                            WHERE      (UsageTypeID = dbo.OEMSailList_Tbl.UsageID)) AS UsageType,
                          (SELECT     Construction
                            FROM          dbo.Construction_Tbl
                            WHERE      (ConstructionID = dbo.OEMSailList_Tbl.ConstructionID)) AS Construction,
                          (SELECT     PartCode
                            FROM          dbo.Fabric_Tbl
                            WHERE      (FabricID = dbo.OEMSailList_Tbl.FabricID)) AS Fabric,
                          (SELECT     ISNULL(DiscountEURO, 0) AS Expr1
                            FROM          dbo.Fabric_Tbl AS Fabric_Tbl_1
                            WHERE      (FabricID = dbo.OEMSailList_Tbl.FabricID)) AS DiscountEuro,
                          (SELECT     OptionName
                            FROM          dbo.Option_Tbl
                            WHERE      (OptionID = dbo.OEMSailList_Tbl.OptionID)) AS OptionName,
                          (SELECT     CASE WHEN BoatRange IS NULL THEN '' ELSE boatRange END + ' ' + CASE WHEN BoatModel IS NULL 
                                                   THEN '' ELSE boatModel END + ' ' + CASE WHEN BoatType IS NULL THEN '' ELSE boatType END AS Expr1
                            FROM          dbo.GenBoatData_Tbl
                            WHERE      (BoatGenID = dbo.OEMSailList_Tbl.BoatGenID)) AS BoatDescription,
                          (SELECT     CASE WHEN
                                                       (SELECT     SUM(oemoptionlist_tbl.optionid)
                                                         FROM          oemoptionlist_Tbl
                                                         WHERE      oemoptionlist_Tbl.oemsaillistid = oemsaillist_Tbl.oemsaillistID) <>
                                                       (SELECT     SUM(quotelineitemoption_tbl.optionid)
                                                         FROM          quotelineitemoption_Tbl
                                                         WHERE      quotelineitemoption_Tbl.quotelineitemid = oemsaillist_Tbl.quotelineitemid) THEN 1 ELSE 0 END AS Expr1) 
                      AS optionintegrity, q.Luff, q.LPorFoot, q.InputArea
FROM         dbo.OEMSailList_Tbl LEFT OUTER JOIN
                      dbo.QuoteLineItem_Tbl AS q ON q.QuoteLineItemID = dbo.OEMSailList_Tbl.QuoteLineItemID